# -*- coding: utf-8 -*-
"""
Created on Tue Jan 12 09:39:47 2021

@author: gcq
"""

import pandas as pd
from urllib.parse import quote
import requests
import json
import pymysql
from sqlalchemy import create_engine


class ResetFlag(object):
    """
    """
    errorCount = 0;

    # 获取区和区 返回df
    def query_st_statis(self, connect):
        select_sql = """
        SELECT imei, city_name, flag FROM st_statis_202012 
        """
        df_data = pd.read_sql(select_sql, connect)
        print("len=", len(df_data))

        bImei = ''
        for i in range(len(df_data)):
            try:
                cImei = str(df_data.iloc[i,0])
                if bImei != cImei :
                    df_data.iloc[i,2] = '是'
                else :
                    df_data.iloc[i,2] = '否'
                bImei = cImei
                #break
            except Exception as e:
                print (e)
        try:
            df_data.to_excel("st_statis.xlsx", sheet_name="Sheet1", encoding='utf-8')
            print("to_excel done")
        except Exception as er:
            print(er)

        return df_data


if __name__ == "__main__":

    mysql_conn=pymysql.connect(host = '172.28.56.77', user = 'mysql', passwd = 'qwert12345', db = 'origin_data')
    cursor=mysql_conn.cursor()
    obj = ResetFlag()
    df_data = obj.query_st_statis(mysql_conn)

    #插入数据#
    engine=create_engine('mysql+pymysql://mysql:qwert12345@172.28.56.77:3306/origin_data?charset=utf8')
    conn=engine.connect()
    print(conn)
    df_data.to_sql('st_statis_202012_copy1',engine,if_exists='append',index = False,index_label = False)
    print('to_sql done')
